package e.odbo.data.model;

import com.openbravo.data.loader.serialize.Datas;
import e.odbo.data.format.Formats;

import java.util.*;


public class Table {

    private String name;
    private List<Column> columnsMap=new ArrayList<>();
    private HashMap<String,String[]> muxIndexs=new HashMap<>();
    private ArrayList<TableForeignKey> foreignKeys=new ArrayList<>();

    private String comment;

    private int tableFlags=I_ColumnGather.Base;

    public Table(String name, Column... columns) {
        this.name = name;

        for(Column column:columns)
            columnsMap.add(column);
    }

    public static Table name(String name){
        return new Table(name);
    }

    public Table(String name){
        this.name = name;
    }

    public Table Column(String name,ColumnType columnType,ColumnFlag columnFlag){
        columnsMap.add(Column.c(name,columnType,columnFlag));
        return this;
    }
    public Table Column(String name,ColumnType columnType,ColumnFlag columnFlag,String comment){
        columnsMap.add(Column.c(name,columnType,columnFlag,comment));
        return this;
    }

    public Table Column(String name,ColumnType columnType,int columnFlag){
        columnsMap.add(Column.c(name,columnType,ColumnFlag.build(columnFlag)));
        return this;
    }
    public Table Column(String name,ColumnType columnType,int columnFlag,String comment){
        columnsMap.add(Column.c(name,columnType,ColumnFlag.build(columnFlag),comment));
        return this;
    }

    public Table Column(String name,ColumnType columnType){
        columnsMap.add(Column.c(name,columnType));
        return this;
    }

    public Table Column(String name,ColumnType columnType,String comment){
        columnsMap.add(Column.c(name,columnType,comment));
        return this;
    }

    public Table addColumn(Column ...columns){
        for(Column column:columns)
           columnsMap.add(column);
        return this;
    }

    public Table comment(String comment){
        this.comment=comment;
        return this;
    }


    public Table ref(Table refTable,boolean cascadeFlag){
        if(cascadeFlag)
            ref(refTable,ForeignKeyFlag.CASCADE,ForeignKeyFlag.CASCADE);
        else
            ref(refTable,ForeignKeyFlag.NO_SET,ForeignKeyFlag.NO_SET);
        return this;
    }

    public Table ref(Table refTable,ForeignKeyFlag cascadeFlag_del,ForeignKeyFlag cascadeFlag_update){
        List<Column> refTablePKColumns=refTable.getPKColumns();
        if(refTablePKColumns.isEmpty())
            return this;
        String colName=null;
        if(refTablePKColumns.size()==1){
            colName=refTable.getName()+"_"+refTablePKColumns.get(0).getName();
            addColumn(Column.c(colName,refTablePKColumns.get(0).getColumnType()).ref(refTable.getName(),refTablePKColumns.get(0).getName()));
            return this;
        }
        String[] columnNames=new String[refTablePKColumns.size()];
        String[] refColNames=new String[refTablePKColumns.size()];
        int i=0;
        for(Column column:refTablePKColumns){
            colName=refTable.getName()+"_"+column.getName();
            this.Column(colName,column.getColumnType());
            columnNames[i]=colName;
            refColNames[i]=column.getName();
            i++;
        }
        ForeignKey(columnNames,refTable.getName(),refColNames,cascadeFlag_del,cascadeFlag_update);
        return this;
    }

    public Table rename(String newName){
        this.name=newName;
        return this;
    }

    //get and set
    public String getName() {
        return name;
    }

    public String getComment(){
        return this.comment;
    }


    public Column[] getColumns() {
        return columnsMap.toArray(new Column[]{});
    }

    public Column[] getColumns(String[] columnNames) {
        Column[] ret=new Column[columnNames.length];
        for(int i=0;i<columnNames.length;i++)
            ret[i]=getColumn(columnNames[i]);
        return ret;
    }

    public Column getColumn(String name){
        for(Column column:columnsMap)
            if(column.getName().equalsIgnoreCase(name))
                return column;
        return null;
    }

    public Table INDEX(String ...cols){
        muxIndexs.put(cols.toString().replace(',','_'),cols);
        return this;
    }

    public Table ForeignKey(String[] columns,String refTable,String[] refColumns){
        foreignKeys.add(new TableForeignKey(this.name,columns,refTable,refColumns));
        return this;
    }

    public Table ForeignKey(String[] columns,String refTable,String[] refColumns,boolean cascadeFlag){
        if(cascadeFlag)
           foreignKeys.add(new TableForeignKey(this.name,columns,refTable,refColumns,ForeignKeyFlag.CASCADE,ForeignKeyFlag.CASCADE));
        else
            foreignKeys.add(new TableForeignKey(this.name,columns,refTable,refColumns));
        return this;
    }

    public Table ForeignKey(String[] columns,String refTable,String[] refColumns,ForeignKeyFlag cascadeFlag_del,ForeignKeyFlag cascadeFlag_update){
        foreignKeys.add(new TableForeignKey(this.name,columns,refTable,refColumns,cascadeFlag_del,cascadeFlag_update));
        return this;
    }

    public Map<String, String[]> getMuxIndexs() {
        return muxIndexs;
    }

    public Map<String, String[]> getIndexs(){
        HashMap<String,String[]> indexs=new HashMap<>(muxIndexs);
        for(Column column:this.getColumns()){
            if(ColumnFlag.isIndex(column.getColumnFlag()))
                indexs.put(column.getName(),new String[]{column.getName()});
        }
        return indexs;
    }

    public ArrayList<TableForeignKey> getForeignKeys(){
        ArrayList<TableForeignKey> allForeignKeys=new ArrayList<TableForeignKey>(this.foreignKeys);
        for(Column column:this.getColumns()) {
            if (column.isRefColumn())
                if(column.isRefCascade())
                    allForeignKeys.add(new TableForeignKey(this.name, new String[]{column.getName()}, column.getRefTable(), new String[]{column.getRefColumn()}, column.getRefCascade_del(),column.getRefCascade_update()));
                 else
                    allForeignKeys.add(new TableForeignKey(this.name, new String[]{column.getName()}, column.getRefTable(), new String[]{column.getRefColumn()}));
        }
        return allForeignKeys;
    }

    public List<Column> getPKColumns(){
        List<Column> pkColumns=new ArrayList<>();
        for(Column column:this.getColumns()) {
            if (ColumnFlag.isPK(column.getColumnFlag()))
              pkColumns.add(column);
        }
        return pkColumns;
    }

    public Table implement(I_ColumnGather ...models){
        for(I_ColumnGather model:models){
            this.tableFlags=tableFlags|model.addTableFlags();
            for(Column column:model.getColumns())
                columnsMap.add(column);
        }
       return this;
    }

    public Datas[] getColumnDatas(){
        Column[] columns=getColumns();
        Datas[] datas=new Datas[columns.length];
        for(int i=0;i<columns.length;i++)
            datas[i]=columns[i].getColumnType().getDataType();
        return datas;
    }

    public Datas[] getColumnDatas(String[] columnNames){
        Column[] columns=getColumns(columnNames);
        Datas[] datas=new Datas[columns.length];
        for(int i=0;i<columns.length;i++)
            datas[i]=columns[i].getColumnType().getDataType();
        return datas;
    }

    public Formats[] getColumnFormats(){
        Column[] columns=getColumns();
        Formats[] formats=new Formats[columns.length];
        for(int i=0;i<columns.length;i++)
            formats[i]=columns[i].getColumnType().format;
        return formats;
    }

    public Formats[] getColumnFormats(String[] columnNames){
        Column[] columns=getColumns(columnNames);
        Formats[] formats=new Formats[columns.length];
        for(int i=0;i<columns.length;i++)
            formats[i]=columns[i].getColumnType().format;
        return formats;
    }



    public static String getListSQL(Table table) {
        StringBuffer sent = new StringBuffer();
        sent.append("select ");
        Column[] columns=table.getColumns();
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sent.append(", ");
            }
            sent.append(columns[i].getName());
        }

        sent.append(" from ");
        sent.append(table.name);

        return sent.toString();
    }

    public static String getListSQL(Table table,String[] cols) {
        StringBuffer sent = new StringBuffer();
        sent.append("select ");
        Column[] columns=table.getColumns(cols);
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sent.append(", ");
            }
            sent.append(columns[i].getName());
        }

        sent.append(" from ");
        sent.append(table.name);

        return sent.toString();
    }


    public static String getInsertSQL(Table table) {

        StringBuffer sent = new StringBuffer();
        StringBuffer values = new StringBuffer();

        sent.append("insert into ");
        sent.append(table.name);
        sent.append(" (");
        Column[] columns=table.getColumns();
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sent.append(", ");
                values.append(", ");
            }
            sent.append(columns[i].getName());
            values.append("?");
        }

        sent.append(") values (");
        sent.append(values.toString());
        sent.append(")");

        return sent.toString();
    }

    public static String getInsertSQL(Table table,String[] cols) {

        StringBuffer sent = new StringBuffer();
        StringBuffer values = new StringBuffer();

        sent.append("insert into ");
        sent.append(table.name);
        sent.append(" (");
        Column[] columns=table.getColumns(cols);
        for (int i = 0; i < columns.length; i++) {
            if (i > 0) {
                sent.append(", ");
                values.append(", ");
            }
            sent.append(columns[i].getName());
            values.append("?");
        }

        sent.append(") values (");
        sent.append(values.toString());
        sent.append(")");

        return sent.toString();
    }


    public static String getUpdateSQL(Table table) {

        StringBuffer values = new StringBuffer();
        StringBuffer filter = new StringBuffer();
        Column[] columns=table.getColumns();

        for (int i = 0; i < columns.length; i++) {
            if (ColumnFlag.isPK(columns[i].getColumnFlag())) {
                if (filter.length() == 0) {
                    filter.append(" where ");
                } else {
                    filter.append(" and ");
                }
                filter.append(columns[i].getName());
                filter.append(" = ?");
            } else {
                if (values.length() > 0) {
                    values.append(", ");
                }
                values.append(columns[i].getName());
                values.append(" = ?");
            }
        }

        return "update " + table.name + " set " + values + filter;
    }

    public static String getDeleteSQL(Table table) {

        StringBuffer filter = new StringBuffer();
        Column[] columns=table.getColumns();
        for (int i = 0; i < columns.length; i++) {
            if (ColumnFlag.isPK(columns[i].getColumnFlag())){
                if (filter.length() == 0) {
                    filter.append(" where ");
                } else {
                    filter.append(" and ");
                }
                filter.append(columns[i].getName());
                filter.append(" = ?");
            }
        }

        return "delete from " + table.name + filter;
    }

    //check PK
    public boolean isPK() {
        for(Column column:columnsMap) {
            if (ColumnFlag.isPK(column.getColumnFlag()))
                return true;
        }
        return false;
    }

    public String[] getColumnNames() {
        String[] columnNames=new String[columnsMap.size()];
        for(int i=0;i<columnsMap.size();i++)
            columnNames[i]=columnsMap.get(i).getName();
        return columnNames;
    }
}
